package com.cennavi.mineplay.data;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.cennavi.mineplay.utils.DateUtils;
import com.mercator.TileUtils;
import com.vividsolutions.jts.geom.Geometry;
import com.vividsolutions.jts.io.WKTReader;

import no.ecc.vectortile.VectorTileEncoder;

@RestController
@RequestMapping(value = "/nankai")
public class NankaiDataService {
	
	@Autowired
	protected JdbcTemplate jdbc;
	
	
	@RequestMapping(value = "/road/{z}/{x}/{y}", produces = "application/x-protobuf")
	public byte[] pbf(@PathVariable int x, @PathVariable int y,
			@PathVariable int z,String pdate, String ptime, String pollutant, int[] cars) {
		
	
		Date today = DateUtils.DateFormatUnit.DAY_AND_MIN.getDateByStr(pdate+ptime);
		String pdt = DateUtils.DateFormatUnit.DAY_AND_MIN.getDateStr(DateUtils.getMinuteByDate(today));
		
		/*String sql = "select a.roadname,a.length,a.id,st_astext(a.geom) as geom,b."+pollutant+" as pollutant from road as a,"
				+ "(select roadid,"+pollutant+" from pollutant_"+pdate+" where date="+pdt+") as b where a.id = b.roadid and  st_intersects(a.geom,st_geomfromtext(?,4326)) ";*/
		String sql = "with a as " + 
				"( " + 
				"select roadname,length,id,geom,kind from road as a " + 
				"where st_intersects(a.geom,st_geomfromtext(?,4326)) " + 
				")," + 
				"b as " + 
				"(" + 
				"select roadid ,"+pollutant+" as pollutant  from pollutant_"+pdate+" where date="+pdt+" " + 
				") " + 
				"select a.roadname,a.length,a.id,st_astext(a.geom) as geom, b.pollutant,a.kind from a,b where a.id = b.roadid ";

		try {

			String tile = TileUtils.parseXyz2Bound(x, y, z);
			
			List<Map<String, Object>> results = jdbc.queryForList(sql, tile);
			
			VectorTileEncoder vte = new VectorTileEncoder(4096, 16, false);

			for (Map<String, Object> m : results) {
				
				String wkt = (String) m.get("geom");
				
				Geometry geom = new WKTReader().read(wkt);
				
				m.remove("geom");

				TileUtils.convert2Piexl(x, y, z, geom);
			
				
				double strn = 0.0;
				double amo = 0.0;
				
				String[] pollu =((String)m.get("pollutant".toLowerCase().toString())).split(",");
				if(cars.length ==1&&cars[0]==0)
				{
					for(int i =0; i<8; i++)
					{
						strn += Double.parseDouble(pollu[i]);
					}
				} else {
				for (int car : cars) {
					strn += Double.parseDouble(pollu[car-1]);
				}
				}
				amo = Double.parseDouble(m.get("length").toString())*strn;
				
				m.put("num", strn);
				m.put("polluAmount",amo);
				
				vte.addFeature("position", m, geom);

			}

			return vte.encode();
		
			 

		} catch (Exception e) {
			e.printStackTrace();
		}

		return null;
	}
	
	@RequestMapping(value = "/reseau/{z}/{x}/{y}", produces = "application/x-protobuf")
	public byte[] reseauPbf(@PathVariable int x, @PathVariable int y,
			@PathVariable int z,String pdate, String ptime, String pollutant, int[] cars) {
		
		Date today = DateUtils.DateFormatUnit.DAY_AND_MIN.getDateByStr(pdate+ptime);
		String pdt = DateUtils.DateFormatUnit.DAY_AND_MIN.getDateStr(DateUtils.getMinuteByDate(today));
		
		String sql = "select a.id,st_astext(a.geom) as geom,b."+pollutant+" as pollutant from reseau as a,"
				+ "(select reseauid,"+pollutant+" from reseau_polltant_"+pdate+" where date="+pdt+") as b where a.id = b.reseauid and  st_intersects(a.geom,st_geomfromtext(?,4326)) ";

		try {

			String tile = TileUtils.parseXyz2Bound(x, y, z);
			
			//System.out.println(tile);
			List<Map<String, Object>> results = jdbc.queryForList(sql, tile);
			
			VectorTileEncoder vte = new VectorTileEncoder(4096, 16, false);

			for (Map<String, Object> m : results) {
				
				String wkt = (String) m.get("geom");
				
				Geometry geom = new WKTReader().read(wkt);
				
				m.remove("geom");

				TileUtils.convert2Piexl(x, y, z, geom);
				
				double strn = 0.0;
				String[] pollu =((String)m.get("pollutant".toLowerCase().toString())).split(",");
				if(cars.length ==1&&cars[0]==0)
				{
					for(int i =0; i<8; i++)
					{
						strn += Double.parseDouble(pollu[i]);
					}
				} else {
				for (int car : cars) {
					strn += Double.parseDouble(pollu[car-1]);
				}
				}
				m.put("num", strn);
				
				vte.addFeature("position", m, geom);

			}

			return vte.encode();
		
			 

		} catch (Exception e) {
			e.printStackTrace();
		}

		return null;
	}
	
	@RequestMapping(value = "/area/{z}/{x}/{y}", produces = "application/x-protobuf")
	public byte[] areaPbf(@PathVariable int x, @PathVariable int y,
			@PathVariable int z,String pdate, String ptime, String pollutant, int[] cars) {
		
		Date today = DateUtils.DateFormatUnit.DAY_AND_MIN.getDateByStr(pdate+ptime);
		String pdt = DateUtils.DateFormatUnit.DAY_AND_MIN.getDateStr(DateUtils.getMinuteByDate(today));
		
		String tile = TileUtils.parseXyz2Bound(x, y, z);
		String sql = "SELECT s.\"id\",(";
		if (cars.length==1&&cars[0]==0){
		    for (int i = 1; i < 9; i++) {
		        sql += "round((SUM (s.arr [ "+i+" ]:: NUMERIC * s.\"length\") / 1000) :: NUMERIC,2) +";
		    }
		}else {
		    for (int i : cars) {
		        sql += "round((SUM (s.arr [ "+i+" ]:: NUMERIC * s.\"length\") / 1000) :: NUMERIC,2) +";
		    }
		}
		sql = sql.substring(0,sql.length()-1);
		sql += ") as num,ar.name, st_astext(ar.geom) as geom from (SELECT ta.\"id\", P .roadid, string_to_array(P ."+pollutant+", ',') AS arr,"+
		"ar.\"length\" FROM ( SELECT A .\"id\",st_astext(st_intersection ( A .geom, st_geomfromtext('"+tile+"', 4326) )) FROM area A WHERE "+ 
		"st_intersects ( A .geom, st_geomfromtext('"+tile+"', 4326) ) ) ta, area_road ar, pollutant_"+pdate+" P WHERE "+
		"ta.\"id\" = ar.areaid AND ar.roadid = P .roadid :: BIGINT AND P .\"date\" = "+pdt+") s, area ar WHERE "+
		"ar.\"id\"=s.\"id\" and ar.type=1 GROUP BY s.\"id\",ar.name,ar.center,geom ;";

		try {
			
			//System.out.println(tile);
			List<Map<String, Object>> results = jdbc.queryForList(sql);
			
			VectorTileEncoder vte = new VectorTileEncoder(4096, 16, false);

			for (Map<String, Object> m : results) {
				
				String wkt = (String) m.get("geom");
				
				Geometry geom = new WKTReader().read(wkt);
				
				m.remove("geom");

				TileUtils.convert2Piexl(x, y, z, geom);
				
				m.put("num", Double.parseDouble(m.get("num").toString()));
				
				vte.addFeature("position", m, geom);

			}
			
		

			return vte.encode();
		
			 

		} catch (Exception e) {
			e.printStackTrace();
		}

		return null;
	}
	
	
	
	@RequestMapping(value = "/area_admin/{z}/{x}/{y}", produces = "application/x-protobuf")
	public byte[] areaAdmin(@PathVariable int x, @PathVariable int y,
			@PathVariable int z) {
		
		 
		
		String tile = TileUtils.parseXyz2Bound(x, y, z);
		String sql = "select st_astext(ar.geom) as geom from area as ar where ar.type=1 and st_intersects ( ar.geom, st_geomfromtext('"+tile+"', 4326) ) ";

		try {
			
			//System.out.println(tile);
			List<Map<String, Object>> results = jdbc.queryForList(sql);
			
			VectorTileEncoder vte = new VectorTileEncoder(4096, 16, false);

			for (Map<String, Object> m : results) {
				
				String wkt = (String) m.get("geom");
				
				Geometry geom = new WKTReader().read(wkt);
				
				m.remove("geom");

				TileUtils.convert2Piexl(x, y, z, geom);
				
				 
				
				vte.addFeature("position", m, geom);

			}
			
		

			return vte.encode();
		
			 

		} catch (Exception e) {
			e.printStackTrace();
		}

		return null;
	}
	
	
	@RequestMapping(value = "/area_ring/{z}/{x}/{y}", produces = "application/x-protobuf")
	public byte[] areaRing(@PathVariable int x, @PathVariable int y,
			@PathVariable int z) {
		
		 
		
		String tile = TileUtils.parseXyz2Bound(x, y, z);
		String sql = "select st_astext(ar.geom) as geom from area as ar where ar.type=2 and st_intersects ( ar.geom, st_geomfromtext('"+tile+"', 4326) ) ";

		try {
			
			//System.out.println(tile);
			List<Map<String, Object>> results = jdbc.queryForList(sql);
			
			VectorTileEncoder vte = new VectorTileEncoder(4096, 16, false);

			for (Map<String, Object> m : results) {
				
				String wkt = (String) m.get("geom");
				
				Geometry geom = new WKTReader().read(wkt);
				
				m.remove("geom");

				TileUtils.convert2Piexl(x, y, z, geom);
				
				 
				
				vte.addFeature("position", m, geom);

			}
			
		

			return vte.encode();
		
			 

		} catch (Exception e) {
			e.printStackTrace();
		}

		return null;
	}
	
	
	@RequestMapping(value = "/ring/{z}/{x}/{y}", produces = "application/x-protobuf")
	public byte[] ringPbf(@PathVariable int x, @PathVariable int y,
			@PathVariable int z,String pdate, String ptime, String pollutant, int[] cars) {
		
		Date today = DateUtils.DateFormatUnit.DAY_AND_MIN.getDateByStr(pdate+ptime);
		String pdt = DateUtils.DateFormatUnit.DAY_AND_MIN.getDateStr(DateUtils.getMinuteByDate(today));
		
		String tile = TileUtils.parseXyz2Bound(x, y, z);
		String sql = "SELECT s.\"id\",(";
		if (cars.length==1&&cars[0]==0){
		    for (int i = 1; i < 9; i++) {
		        sql += "round((SUM (s.arr [ "+i+" ]:: NUMERIC * s.\"length\") / 1000) :: NUMERIC,2) +";
		    }
		}else {
		    for (int i : cars) {
		        sql += "round((SUM (s.arr [ "+i+" ]:: NUMERIC * s.\"length\") / 1000) :: NUMERIC,2) +";
		    }
		}
		sql = sql.substring(0,sql.length()-1);
		sql += ") as num,ar.name, st_astext(ar.geom) as geom from (SELECT ta.\"id\", P .roadid, string_to_array(P ."+pollutant+", ',') AS arr,"+
		"ar.\"length\" FROM ( SELECT A .\"id\",st_astext(st_intersection ( A .geom, st_geomfromtext('"+tile+"', 4326) )) FROM area A WHERE "+ 
		"st_intersects ( A .geom, st_geomfromtext('"+tile+"', 4326) ) ) ta, area_road ar, pollutant_"+pdate+" P WHERE "+
		"ta.\"id\" = ar.areaid AND ar.roadid = P .roadid :: BIGINT AND P .\"date\" = "+pdt+") s, area ar WHERE "+
		"ar.\"id\"=s.\"id\" and ar.type=2 GROUP BY s.\"id\",ar.name,ar.center,geom ;";

		
		try {
			
			//System.out.println(tile);
			List<Map<String, Object>> results = jdbc.queryForList(sql);
			
			VectorTileEncoder vte = new VectorTileEncoder(4096, 16, false);

			for (Map<String, Object> m : results) {
				
				String wkt = (String) m.get("geom");
				
				Geometry geom = new WKTReader().read(wkt);
				
				m.remove("geom");

				TileUtils.convert2Piexl(x, y, z, geom);
				
				m.put("num", Double.parseDouble(m.get("num").toString()));
				
				vte.addFeature("position", m, geom);

			}

			return vte.encode();
		
			 

		} catch (Exception e) {
			e.printStackTrace();
		}

		return null;
	}
	
	@RequestMapping(value="/roadpollu")
	public Object rdPollu(String pdate, String ptime, String pollutant, int[] cars, String roadId, Double length) {
		
		Date today = DateUtils.DateFormatUnit.DAY_AND_MIN.getDateByStr(pdate+ptime);
		Date yesday = DateUtils.addDays(today, -1);
		String pdt = DateUtils.DateFormatUnit.DAY_AND_MIN.getDateStr(DateUtils.getMinuteByDate(today));
		String befPdt = DateUtils.DateFormatUnit.DAY_AND_MIN.getDateStr(DateUtils.getMinuteByDate(yesday));
		String befPdate = befPdt.substring(0,8);
		
		String str = "";
		if (cars.length==1&&cars[0]==0){
		    for (int i = 1; i < 9; i++) {
		        str+= "AVG (s.arr [ "+i+" ]:: NUMERIC) +";
		    }
		}else {
		    for (int i : cars) {
		        str+="AVG (s.arr [ "+i+" ]:: NUMERIC) +";
		    }
		}
		str = str.substring(0,str.length()-1);
		String sql = "SELECT SUBSTRING ( s.\"date\" :: VARCHAR FROM 1 FOR 10 ) AS TIME,round(("+str+
				    ")*"+length+",2) as num FROM ( SELECT \"date\", string_to_array("+pollutant+", ',') AS arr FROM pollutant_"+befPdate+" WHERE roadid = '"+roadId+"' "+
					"AND \"date\" >= "+befPdt+" ) AS s GROUP BY TIME UNION ALL ( SELECT SUBSTRING ( s.\"date\" :: VARCHAR FROM 1 FOR 10 "+
					") AS TIME,round(("+str+")*"+length+",2) as num FROM ( SELECT \"date\", string_to_array("+pollutant+", ',') AS arr FROM pollutant_"+pdate+" "+
					"WHERE roadid = '"+roadId+"' AND \"date\" <= "+pdt+") AS s GROUP BY TIME ) ORDER BY TIME";

		try {
			List<Map<String, Object>> results = jdbc.queryForList(sql);	
			
			List<Object> listData = new ArrayList<Object>();
			List<Object> listaxisText = new ArrayList<Object>();
			
			Map<String,Object> map = new HashMap<String,Object>();
			
			map.put("data", listData);
			map.put("axisText", listaxisText);
			
			for(Map<String,Object> m : results) {
				listData.add(m.get("num"));
				listaxisText.add(m.get("time"));
			}


			return map;

		} catch (Exception e) {
			e.printStackTrace();
		}

		return null;
	}
	
	//环线和区域公用（区域内道路排放量Top5）
	@RequestMapping(value="/areapollu")
	public Object areaPollu(String pdate, String ptime, String pollutant, int[] cars, String areaId) {
		
		Date today = DateUtils.DateFormatUnit.DAY_AND_MIN.getDateByStr(pdate+ptime);
		String pdt = DateUtils.DateFormatUnit.DAY_AND_MIN.getDateStr(DateUtils.getMinuteByDate(today));
		
		
		String str = "";
		if (cars.length==1&&cars[0]==0){
		    for (int i = 1; i < 9; i++) {
		        str+= " (s.array ["+i+"]:: NUMERIC) +";
		    }
		}else {
		    for (int i : cars) {
		        str+="(s.array ["+i+"]:: NUMERIC) +";
		    }
		}
		str = str.substring(0,str.length()-1);
		String sql = "SELECT s.roadid, s.roadname || '-' || COALESCE (s.startname, '无名路') || '-' || COALESCE (s.endname, '无名路') AS NAME,round((("+str+
				")*s.length/1000)::numeric,2) AS num FROM ( SELECT C.roadname,C.startname,C.endname,b.roadid,string_to_array(b."+pollutant+", ',') AS array,A.length FROM area_road A, pollutant_"+pdate+" b,road C WHERE "+
				"A.roadid = b.roadid :: BIGINT AND A.roadid = C.ID :: BIGINT AND A.areaid = '"+areaId+"' AND b.\"date\" = "+pdt+") s ORDER BY num DESC LIMIT 5 OFFSET 0";
			
		try {
			List<Map<String, Object>> results = jdbc.queryForList(sql);	

			return results;

		} catch (Exception e) {
			e.printStackTrace();
		}

		return null;
	}
	

}
